![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
TriggersAnother way to ensure and verify data integrity is with the use of triggers. A trigger is a procedure stored in the database; it is fired whenever a table is modified. Triggers can be used to enforce integrity constraints and database security. A trigger is created and defined to fire either before or after a modification to the table has occurred. To create a trigger, use the Oracle CREATE TRIGGER command. By creating triggers on database objects, you can further ensure data integrity. Although triggers are typically used to enforce referential integrity, there are other uses for triggers:
Using TriggersThe preceding list gave several uses of triggers. The following list shows a few ways triggers can be used in applications:
Using AlertsWhen using triggers, you can take advantage of the RDBMS_ALERT package supplied by Oracle. With this package, you can enable the use of asynchronous notification of database events. With the DBMS_ALERT package, applications can be signaled if an event occurs in the databasea useful function for applications that need an event to occur before proceeding or for applications that signal error conditions that have occurred. Waiting for events to occur asynchronously is much more efficient than polling the database to see whether the event has occurred. When you poll, you generate network traffic and execute SQL statements unnecessarily. If your application must wait for an event, using the RDBMS_ALERT package is the way to do it. Creating TriggersTriggers are created in a manner almost identical to stored procedures. The syntax for a trigger begins with these keywords: CREATE OR REPLACE TRIGGER trigger_name These keywords are followed by the WHEN clause, which determines when the trigger is fired. The WHEN clause can consist of these keywords: BEFORE or AFTER Following these keywords are the qualifying statement(s): DELETE or INSERT or UPDATE The qualifying statements are followed by this clause: ON table_name In the case of an UPDATE, you can specify column names. The addition of this statement specifies that the trigger will fire for each row that is accessed, rather than on a statement basis: FOR EACH ROW Here is an example of a trigger that uses the familiar DOGS table: SQL> CREATE OR REPLACE TRIGGER old_one 2 AFTER INSERT OR UPDATE OR DELETE ON dogs 3 FOR EACH ROW 4 BEGIN 5 IF (:new.age > 8) THEN 6 RDBMS_OUTPUT.PUT_LINE('Note: Dog is older than 8 years.'); 7 END IF; 8 END; 9 / Trigger created. Now if you insert a dog into the DOGS table that is older than 8 years old, you get the following output: SQL> INSERT INTO dogs 2 ( dogname, age, breed, owner ) 3 VALUES 4 ( 'Molly', 14, 5, 'Hanks' ); Note: Dog is older than 8 years. 1 row created.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |